Skip to main content
Version: 8.5.11.1

TickerDefinitionExt

V8 Message Definiton

TickerDefinitionExt (external) records exist for all SpiderRock tickers including equity tickers (stocks and ETFs) as well as index tickers and synthetic tickers for future chains and option multihedge baskets.

METADATA​

AttributeValue
Topic4335-product-definition
MLink TokenInternal
ProductSRLive
accessTypeSELECT

Table Definition​

FieldTypeKeyDefault ValueComment
ticker_atenum - AssetTypePRI'None'
ticker_tsenum - TickerSrcPRI'None'
ticker_tkVARCHAR(12)PRI''
symbolTypeenum - SymbolType'None'
nameVARCHAR(72)''Symbol name
issuerNameVARCHAR(72)''Name of issuer
cntryOfIncorpVARCHAR(2)''ISO Issuer Country Code
parValueFLOAT0Security Parvalue
parValueCurrencyVARCHAR(3)''Security Parvalue currency
pointValueFLOAT0
pointCurrencyenum - Currency'None'
priceFormatenum - PriceFormat'None'PriceFormat ex NMSPenny NMSHalfPenny
minTickSizeFLOAT0
primaryExchenum - PrimaryExchange'None'
altIDINT0Alt Security ID number
micVARCHAR(4)''ISO Market Identification Code
micSegVARCHAR(4)''ISO Market Indentification Segment Code
symbolVARCHAR(12)''stock symbol
issueClassVARCHAR(1)''issue class of stock symbol if no issue class field will be blank
securityIDINT0Security ID number from the source Vendor SR Feed
sicVARCHAR(4)''SIC Standard Industrial Classification code
cikVARCHAR(10)''Central Index Key US specific
gicsVARCHAR(8)''Global Industry Classification Standard
leiVARCHAR(20)''Legal Entity Identifier
naicsVARCHAR(6)''North American Industry Classification System
cfiVARCHAR(6)''ISO Classification of Financial Instruments
cicVARCHAR(4)''Complementay Identification Code
fisnVARCHAR(40)''Financial Instrument Short Name
isinVARCHAR(12)''ISIN code
bbgCompositeTickerVARCHAR(12)''Bloomberg Composite Ticker
bbgExchangeTickerVARCHAR(28)''Bloomberg Exchange Ticker
bbgCompositeGlobalIDVARCHAR(12)''Bloomberg Composite Global ID
bbgGlobalIDVARCHAR(12)''Bloomberg Global ID
bbgCurrencyVARCHAR(3)''Bloomberg Trading Currency
stkVolumeFLOAT0daily stock volume
futVolumeFLOAT0daily future volume
optVolumeFLOAT0daily option volume
exchStringVARCHAR(8)''exchanges listing any options on this underlying
hasOptionsenum - YesNo'None'Has Options flag
numOptionsINT0total number of listed options
roundlotINT0round lot
sharesOutstandingBIGINT0symbol shares outstanding represented in thousands actualsharesoutstanding sharesoutstanding 1000
otcPrimaryMarketenum - OTCPrimaryMarket'None'
otcTierenum - OTCTier'None'
otcReportingStatusVARCHAR(1)''
otcDisclosureStatusINT0
otcFlagsINT0
tkDefSourceenum - TkDefSource'None'Ticker definition source None Vendor OTC SR Exchange
statusFlagenum - TkStatusFlag'None'
timeMetricenum - TimeMetric'None'trading time metric 252 or 365 trading days or a weekly cycle type
tradingPeriodenum - TradingPeriod'None'
timestampDATETIME(6)'1900-01-01 00:00:00.000000'

PRIMARY KEY DEFINITION (Unique)​

FieldSequence
ticker_tk1
ticker_at2
ticker_ts3

CREATE TABLE EXAMPLE QUERY​

CREATE TABLE `SRLive`.`MsgTickerDefinitionExt` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `symbolType` ENUM('None','Equity','ADR','ETF','CashIndex','MutualFund','ShortETF','Future','Bond','DepReceipts','PreferredSec','PreferenceShare','StructuredProd','StapledSec','TradeableRights','Unit','Warrant','WhenIssued','ForeignIssue') NOT NULL DEFAULT 'None',
    `name` VARCHAR(72) NOT NULL DEFAULT '' COMMENT 'Symbol name',
    `issuerName` VARCHAR(72) NOT NULL DEFAULT '' COMMENT 'Name of issuer',
    `cntryOfIncorp` VARCHAR(2) NOT NULL DEFAULT '' COMMENT 'ISO Issuer Country Code',
    `parValue` FLOAT NOT NULL DEFAULT 0 COMMENT 'Security Parvalue',
    `parValueCurrency` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Security Parvalue currency',
    `pointValue` FLOAT NOT NULL DEFAULT 0,
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX','DKK','GEL') NOT NULL DEFAULT 'None',
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','V16','A0','A1','A2','A3','A4','A5','A6','A7','E32','EU2A','EU2B','EU2C','EU2D','EU2E','EU2F','EU3A','EU3B','EU3C','EU3D','EU3E','EU3F','EU3G','EU3H','EU3I','EU3J','EU4A','EU4B','EU4C','EU5A','EU5B','NMS_Penny','NMS_Nickle','NMS_HalfPenny','EQT') NOT NULL DEFAULT 'None' COMMENT 'PriceFormat: ex: NMS_Penny, NMS_HalfPenny',
    `minTickSize` FLOAT NOT NULL DEFAULT 0,
    `primaryExch` ENUM('None','NYSE','NYSEArca','NYSEMkt','NASDAQ','NASDAQBOS','BATS','PHLX','IEXG','CSE','NSE','FINRA','PORTAL','OTC','CME','CBOT','NYMEX','COMEX','ICE','EUREX','XETRA','CDEX','BXE','CXE','DXE','LSE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','XJSE','BERA','FRAA','HAMA','XWBO','DUSA','XSWX','XWAR','FNSE','XGAT','XSTO','XTAE','XCYS','XPRA','HANA','BOTC','NXDUB','XATH','XMAD','XCSE','FNDK','XTAL','FNEE','XHEL','NMO','TRQX','XICE','FNIS','XLIT','FNLT','XRIS','FNLV','XSAT','MUNA','XETA','XLU','XBD') NOT NULL DEFAULT 'None',
    `altID` INT NOT NULL DEFAULT 0 COMMENT 'Alt Security ID number',
    `mic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ISO Market Identification Code',
    `micSeg` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ISO Market Indentification Segment Code',
    `symbol` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'stock symbol',
    `issueClass` VARCHAR(1) NOT NULL DEFAULT '' COMMENT 'issue class of stock symbol.  if no issue class field will be blank.',
    `securityID` INT NOT NULL DEFAULT 0 COMMENT 'Security ID number from the source - Vendor, SR, Feed',
    `sic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'SIC (Standard Industrial Classification) code',
    `cik` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'Central Index Key (US specific)',
    `gics` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'Global Industry Classification Standard',
    `lei` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Legal Entity Identifier',
    `naics` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'North American Industry Classification System',
    `cfi` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'ISO Classification of Financial Instruments',
    `cic` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'Complementay Identification Code',
    `fisn` VARCHAR(40) NOT NULL DEFAULT '' COMMENT 'Financial Instrument Short Name',
    `isin` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'ISIN code',
    `bbgCompositeTicker` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Composite Ticker',
    `bbgExchangeTicker` VARCHAR(28) NOT NULL DEFAULT '' COMMENT 'Bloomberg Exchange Ticker',
    `bbgCompositeGlobalID` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Composite Global ID',
    `bbgGlobalID` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'Bloomberg Global ID',
    `bbgCurrency` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Bloomberg Trading Currency',
    `stkVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily stock volume',
    `futVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily future volume',
    `optVolume` FLOAT NOT NULL DEFAULT 0 COMMENT 'daily option volume',
    `exchString` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'exchanges listing any options on this underlying',
    `hasOptions` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'Has Options flag',
    `numOptions` INT NOT NULL DEFAULT 0 COMMENT 'total number of listed options',
    `roundlot` INT NOT NULL DEFAULT 0 COMMENT 'round lot',
    `sharesOutstanding` BIGINT NOT NULL DEFAULT 0 COMMENT 'symbol shares outstanding, represented in thousands (actualsharesoutstanding = sharesoutstanding * 1000)',
    `otcPrimaryMarket` ENUM('None','OTCLink','OTCBB','OTCLinkBB','GreyMarket','OTCBonds') NOT NULL DEFAULT 'None',
    `otcTier` ENUM('None','OtcNoTier','OTCQXUSPrem','OTCQXUS','OTCQXIntPrem','OTCQXInt','OTCQB','OTCBBOnly','PinkCurr','PinkLim','PinkNoInfo','Grey','Expert','OTCBonds') NOT NULL DEFAULT 'None',
    `otcReportingStatus` VARCHAR(1) NOT NULL DEFAULT '',
    `otcDisclosureStatus` INT NOT NULL DEFAULT 0,
    `otcFlags` INT NOT NULL DEFAULT 0,
    `tkDefSource` ENUM('None','Vendor','OTC','SR','Exchange') NOT NULL DEFAULT 'None' COMMENT 'Ticker definition source: None; Vendor; OTC; SR; Exchange',
    `statusFlag` ENUM('None','Active','Delisted') NOT NULL DEFAULT 'None',
    `timeMetric` ENUM('None','D252','D365','SRV6') NOT NULL DEFAULT 'None' COMMENT 'trading time metric - 252 or 365 trading days or a weekly cycle type',
    `tradingPeriod` ENUM('None','NMS','NMS_EXT','NMS_GTH','CME_ES','CME_GRAIN','CME_TRSY','CME_ENGY','CME_METAL','CME_FX','CME_COMD','CME_CRYP','CME_DAIRY','CME_EQBTIC','CME_NKBTIC','CME_WEATHER','CME_TACO','CME_TPXBTIC','CME_FTSE','CME_BMD','CME_BOVESPA','CME_EQTMAC','CME_TAM','CME_OTHER','CFE','ICE_US','ICE_EU','EUX','EU_ERX','EU_CBOE','EU_NXAM','EU_NXBR','EU_NXLS','EU_NXML','EU_NXOS','EU_NXP','EU_AT','EU_BE','EU_CH','EU_DE','EU_DK','EU_FI','EU_FR','EU_GB','EU_IE','EU_IT','EU_NL','EU_NO','EU_PT','EU_SE') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='TickerDefinitionExt (external) records exist for all SpiderRock tickers including equity tickers (stocks and ETFs) as well as index tickers and synthetic tickers for future chains and option multihedge baskets.';

SELECT TABLE EXAMPLE QUERY​

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `symbolType`,
    `name`,
    `issuerName`,
    `cntryOfIncorp`,
    `parValue`,
    `parValueCurrency`,
    `pointValue`,
    `pointCurrency`,
    `priceFormat`,
    `minTickSize`,
    `primaryExch`,
    `altID`,
    `mic`,
    `micSeg`,
    `symbol`,
    `issueClass`,
    `securityID`,
    `sic`,
    `cik`,
    `gics`,
    `lei`,
    `naics`,
    `cfi`,
    `cic`,
    `fisn`,
    `isin`,
    `bbgCompositeTicker`,
    `bbgExchangeTicker`,
    `bbgCompositeGlobalID`,
    `bbgGlobalID`,
    `bbgCurrency`,
    `stkVolume`,
    `futVolume`,
    `optVolume`,
    `exchString`,
    `hasOptions`,
    `numOptions`,
    `roundlot`,
    `sharesOutstanding`,
    `otcPrimaryMarket`,
    `otcTier`,
    `otcReportingStatus`,
    `otcDisclosureStatus`,
    `otcFlags`,
    `tkDefSource`,
    `statusFlag`,
    `timeMetric`,
    `tradingPeriod`,
    `timestamp`
FROM `SRLive`.`MsgTickerDefinitionExt`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk';

Doc Columns Query​

SELECT * FROM SRLive.doccolumns WHERE TABLE_NAME='TickerDefinitionExt' ORDER BY ordinal_position ASC;